package net.cmkj.mine.bean;

import com.jfinal.aop.Before;
import com.jfinal.kit.PathKit;
import com.jfinal.kit.PropKit;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.activerecord.tx.Tx;
import net.cmkj.mine.common.model.InvokeResult;
import net.cmkj.mine.interceptor.MineInterceptor;
import net.cmkj.mine.util.BeanUtils;
import net.cmkj.mine.util.CommonUtils;
import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;

import java.io.File;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class Mine {

    private static Logger log = LogManager.getLogger(Mine.class);

    /**
     * 获取列表（分页）
     * @param pageNumber
     * @param pageSize
     * @param moduleid
     * @param tablename
     * @param args
     * @param orderStr
     * @return
     * @throws Exception
     */
    @Before(MineInterceptor.class)
    public Page<Record> getList(int pageNumber, int pageSize, long moduleid, String tablename, List<Record> args, String orderStr) throws Exception {
        List<Object> params = new ArrayList<Object>();
        StringBuffer select = new StringBuffer();
        StringBuffer sqlExceptSelect=new StringBuffer();
        makeArguments(moduleid, tablename, args, select, sqlExceptSelect, params);
        List<Record> records = Db.find("SELECT * FROM `t_order` where moduleid = ?", moduleid);
        if(records.size()>0){
            String orderby = " ORDER BY ";
            for (int i = 0; i < records.size(); i++) {
                if(i!=0){
                    orderby +=" , ";
                }
                Record record = records.get(i);
               String fieldname = record.getStr("column_name");
               Integer type =  record.getInt("order");
               if(type==1){
                   orderby +=" "+fieldname+" "+" asc ";
               }else if(type==2){
                   orderby +=" "+fieldname+" "+" desc ";
               }
            }
            sqlExceptSelect.append(" "+orderby);
        }else{
            sqlExceptSelect.append(" "+orderStr);
        }

        System.err.println(sqlExceptSelect.toString());
        return Db.paginate(pageNumber, pageSize, select.toString(), sqlExceptSelect.toString(), CommonUtils.listToArr(params));
    }

    /**
     * 获取列表
     * @param moduleid
     * @param tablename
     * @param args
     * @param orderStr
     * @return
     * @throws Exception
     */
    public List<Record> getList(long moduleid, String tablename, List<Record> args, String orderStr) throws Exception {
        List<Object> params = new ArrayList<Object>();
        StringBuffer select = new StringBuffer();
        StringBuffer sqlExceptSelect=new StringBuffer();
        makeArguments(moduleid, tablename, args, select, sqlExceptSelect, params);
        sqlExceptSelect.append(" "+orderStr);
        return Db.find(select.toString()+sqlExceptSelect.toString(), CommonUtils.listToArr(params));
    }

    /**
     * 获取对象
     * @param tablename
     * @param id
     * @return
     * @throws Exception
     */
    public Record findById(String tablename, long id) throws Exception {
        return Db.findFirst("select t.* from "+tablename+" t where t.id = ?", id);
    }

    /**
     * 对象保存
     * @param tablename
     * @param record
     * @return
     * @throws Exception
     */
    public InvokeResult save(String tablename, Record record,SysUser user) throws Exception {
        log.info("对象保存");
        boolean flag = false;
        if (null == record.getLong("id")) {
            record.set("createuser",user.getId());
            record.set("createdate",new Date());
            record.set("deptid",user.getDeptid());
            flag = Db.save(tablename, record);
        } else {
            Record src = findById(tablename, record.getLong("id"));
            log.info(BeanUtils.compare(src, record));
            flag = Db.update(tablename, record);
        }
        if (flag) {
            return InvokeResult.success();
        } else {
            return InvokeResult.failure("对象保存失败！");
        }
    }

    /**
     * 对象删除
     * @param tablename
     * @param ids
     * @return
     * @throws Exception
     */
    @Before(Tx.class)
    public InvokeResult deleteByIds(String tablename, String ids) throws Exception{
        log.info("对象删除");
        String idarr[] = ids.split(",");
        for (String id : idarr) {
            if (!Db.deleteById(tablename, id)) {
                log.error("批量删除失败！");
                throw new RuntimeException(id+"批量删除失败");
            }
        }
        return InvokeResult.success();
    }
    /**
    * 对象删除
     * @param tablename
     * @param ids
     * @return
     * @throws Exception
     */
    @Before(Tx.class)
    public InvokeResult deleteFlieByIds(String tablename, String ids) throws Exception{
        log.info("对象删除");
        String idarr[] = ids.split(",");
        for (String id : idarr) {
            Record record = Db.findFirst("select t.* from "+tablename+"_attach t where t.id = ?", id);
            if (!Db.deleteById(tablename+"_attach", id)) {
                log.error("批量删除失败！");
                throw new RuntimeException(id+"批量删除失败");
            }
            String path = PathKit.getWebRootPath()+"/upload/"+tablename+"/"+record.get("realname");
            File file = new File(path);
            file.delete();
        }
        return InvokeResult.success();
    }

    /**
     * 获取附件列表
     * @param tablename
     * @param parentid
     * @return
     * @throws Exception
     */
    public List<Record> getAttachList(String tablename, long parentid) throws Exception {
        log.info("获取附件列表");
        return Db.find("select t.* from "+tablename+"_attach t where t.parentid = ?", parentid);
    }

    /**
     * 获取附件
     * @param tablename
     * @param id
     * @return
     * @throws Exception
     */
    public Record getAttach(String tablename, long id) throws Exception {
        log.info("获取附件");
        return Db.findFirst("select t.* from "+tablename+"_attach t where t.id = ?", id);
    }

    /**
     * 获取已设置查询字段列表
     * @param moduleid
     * @return
     * @throws Exception
     */
    public List<Columns> getSearchedList(long moduleid, String columnids) throws Exception {
        log.info("获取已设置查询字段列表");
        try {
            if (StrKit.notBlank(columnids)) {
                return Columns.dao.find("select t.* from columns t where t.moduleid = ? and t.id in ("+columnids+")", moduleid);
            } else {
                return new ArrayList<Columns>();
            }
        } catch (Exception e) {
            e.printStackTrace();
            return new ArrayList<Columns>();
        }
    }

    /**
     * 获取下拉选择框
     * @param table
     * @param columnname
     * @return
     * @throws Exception
     */
    public List<Record> getSelectData(String table, String columnname) throws Exception {
        return Db.find("select t.id as value, t."+columnname+" as name from "+table+" t order by t.id");
    }

    private void makeArguments(long moduleid, String tablename, List<Record> args, StringBuffer select, StringBuffer sqlExceptSelect, List<Object> params) throws Exception {
        sqlExceptSelect.append(" from "+tablename+" t  left join sys_user u on t.createuser = u.id ");
        select.append("select t.* ,u.username");
        StringBuffer ses = new StringBuffer();
        List<Modules> foretables = getForeignTable(moduleid);
        if (foretables != null && foretables.size() > 0) {
            for (int i = 0; i < foretables.size(); i++) {
                Modules foretable = foretables.get(i);
                //Columns column = Columns.dao.findByPosition(foretable.getLong("foreignTable"), 2);
                String forecolumn = foretable.getStr("foreignKey");
                if (StrKit.notBlank(forecolumn)) {
                    String forecolumnname = foretable.getStr("column_name");
                    select.append(", f"+i+"."+forecolumn+" as "+forecolumnname+" ");
                    ses.append(" left join "+foretable.getTablename()+" f"+i+" on t."+forecolumnname+ " = f"+i+".id");
                }
            }
        }
        sqlExceptSelect.append(ses);
        sqlExceptSelect.append(" where 1 = 1 ");
        if (args != null && args.size() > 0) {
            for (Record arg : args) {
                String key = arg.getStr("key");
                String value = arg.getStr("value");
                String type = arg.getStr("type");
                if (StrKit.notBlank(value)) {
                    if ("varchar".equalsIgnoreCase(type) || "text".equalsIgnoreCase(type) || "longtext".equalsIgnoreCase(type)) {
                        sqlExceptSelect.append(" and t."+key+" like ? ");
                        params.add("%"+value+"%");
                    } else if ("date".equalsIgnoreCase(type) || "datetime".equalsIgnoreCase(type)) {
                        String[] dates = value.split(" - ");
                        sqlExceptSelect.append(" and date_format(t."+key+", '%Y-%m-%d') >= ? and date_format(t."+key+", '%Y-%m-%d') <= ? ");
                        params.add(dates[0]);
                        params.add(dates[1]);
                    } else {
                        sqlExceptSelect.append(" and t."+key+" = ? ");
                        params.add(value);
                    }
                }
            }
        }
    }

    public List<Modules> getForeignTable(long moduleid) throws Exception {
        String table = PropKit.use("table.txt").get("table");
        return Modules.dao.find("select m.name, concat('"+table+"', m.tablename) as tablename, t.column_name, t.foreignTable, t.foreignKey from columns t left join modules m on t.foreignTable = m.id where t.moduleid = ? and t.foreignTable != ''", moduleid);
    }

    /**
     * 新增或者修改
     */
    public Boolean insertOrUpdate(String tableName,Record record){
//        {field0001:2022-05-05, field0002:1000.0, field0003:2, field0004:马姗姗, field0005:222, field0006:1, id:2}

//        insert into cmkj0038(id,field0001,field0002,field0003,field0004,field0005,field0006,createuser,createdate)
//        values('10','2022-05-05','1000.0','2','马姗姗','1111','1','1','2022-05-05')
//        ON DUPLICATE KEY UPDATE
//        field0001 = values(field0001),
//                field0002 = values(field0002),
//                field0003 = values(field0003),
//                field0004 = values(field0004),
//                field0005 = values(field0005),
//                field0006 = values(field0006)

        String sql="insert into "+tableName;
        String field="(";
        String value="values(";
        String updateValue="";
        for (String s : record.getColumns().keySet()) {
            field += s + ",";
            value += "'"+record.getColumns().get(s)+"',";
            updateValue += s +"=values("+s+"),";
        }
        field = field.substring(0,field.length()-1) + ")";
        value = value.substring(0,value.length()-1) + ")";
        updateValue = "ON DUPLICATE KEY UPDATE " + updateValue.substring(0,updateValue.length()-1);
        int update = Db.update(sql + field + value + updateValue);
        if (update <= 0){
            return false;
        }
        return true;
    }

}
